import type { Router } from "express";
import type { Ioracle, IcallbackFn } from "../../types";
import { decrypt } from "../../utils/privateKey";
import { BindParameters } from "oracledb";
import { SendMailLoop } from "./interview";
import { sendMail } from "./mail";

interface ParamType {
  docid: string;
  seq: number;
  improvement_theme: string;
  improvement_result: string;
  interview_date: string;
  last_update_by: string;
  operatoin: string; //修改或添加
  type: string;
  sendtype: string; //送出或保存
  flow_code: string;
  approve: string;
  lines: ImproveDataLines[];
}

interface ImproveDataLines {
  id: number | string;
  project: string;
  purpose: string;
  limit_start: string;
  limit_end: string;
  result?: string;
  is_adopt?: string;
}

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getuinfo", (req, res) => {
    const { docid, username, seq, type } = req.body;
    let sql = "";
    if (type === "end") {
      sql =
        " select a.no badge,a.name_in_chinese chinese_name,decode(a.spend_type,'IDL','間接員工','直接員工')spend_type,'Y' sign_flag,'Y' sign_result_flag";
      sql +=
        " ,b.description position,a.dept_code,to_char(a.hire_date,'yyyy-mm-dd')hire_date,'Y' manager_save_flag,'Y' user_accept_flag,'end' flow_code,'結案' flow_phase";
      sql +=
        " from personnel_basic a, job_position b,xxhr_improve_achieve_header c,xxhr_improve_acheve_improve d";
      sql += ` where a.pos_code = b.code and a.no = c.badge and c.docid ='${docid}'`;
      sql += " and c.docid = d.docid ";
      sql += ` and d.seq = ${seq}`;
    } else {
      sql =
        " select a.no badge,a.name_in_chinese chinese_name,decode(a.spend_type,'IDL','間接員工','直接員工')spend_type,d.sign_flag,d.sign_result_flag";
      sql +=
        " ,b.description position,a.dept_code,to_char(a.hire_date,'yyyy-mm-dd')hire_date,e.flow_code,e.flow_phase,d.manager_save_flag,d.user_accept_flag";
      sql +=
        " from personnel_basic a, job_position b,xxhr_improve_achieve_header c,xxhr_improve_acheve_improve d,xxhr_improve_achieve_flow e";
      sql += ` where a.pos_code = b.code and a.no = c.badge and c.docid ='${docid}'`;
      sql +=
        " and c.docid = d.docid and c.status = 'N' and c.docid = e.docid and c.current_flow = e.flow ";
      sql += ` and e.klm = '${decrypt(username)}'`;
      sql += ` and d.seq = ${seq}`;
    }
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getseq", (req, res) => {
    const { docid } = req.body;
    let sql = `select count(*) count from( select count(*)count from xxhr_improve_acheve_improve where docid = '${docid}' group by seq)group by count `;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/addlines", async (req, res) => {
    const { param } = req.body;
    const paramP: ParamType = JSON.parse(param);
    let sql = "";
    let bind: BindParameters[] = [];
    try {
      sql = ` delete from xxhr_improve_acheve_improve_l where docid='${paramP.docid}' and seq = ${paramP.seq}`;
      await oracle.connHrm({ sql });
      for (let i = 0; i < paramP.lines.length; i++) {
        let obj = {};
        Reflect.set(obj, "docid", paramP.docid);
        Reflect.set(obj, "seq", paramP.seq);
        Object.entries(paramP.lines[i]).map(([key, value]) => {
          if (key !== "id") Reflect.set(obj, key, value);
        });
        bind.push(obj);
      }
      sql = ` insert into xxhr_improve_acheve_improve_l (docid,seq,project,purpose,limit_start,limit_end,last_update_time ${
        paramP.flow_code === "improve_check" ? ",result,is_adopt" : ""
      })`;
      sql += ` values(:docid,:seq,:project,:purpose,to_date(:limit_start,'yyyy-mm-dd'),to_date(:limit_end,'yyyy-mm-dd'),sysdate ${
        paramP.flow_code === "improve_check" ? ",:result,:is_adopt" : ""
      })`;
      await oracle.connHrm({ sql, isMulti: true, params: bind });

      sql = " update xxhr_improve_acheve_improve set manager_save_flag = 'Y'";
      sql += ` ,improvement_theme='${
        paramP.improvement_theme
      }',interview_date = sysdate,last_update_by='${decrypt(
        paramP.last_update_by
      )}',last_update_time=sysdate`;
      if (paramP.flow_code === "improve_check") {
        sql += ` ,improvement_result ='${paramP.improvement_result}'`;
      }
      sql += ` where docid ='${paramP.docid}' and seq = ${paramP.seq}`;
      await oracle.connHrm({ sql });
      if (paramP.sendtype === "save") {
        res.send({ code: 200, message: "success", data: [] });
      } else {
        sql = ` begin xxhr_improve_achieve_approve_p('${
          paramP.docid
        }','${decrypt(paramP.last_update_by)}','','${paramP.approve}',${
          paramP.seq
        });end;`;
        await oracle.connHrm({ sql });
        sql =
          " select b.docid,b.flow_phase,substr(b.klm,9) || '@compeq.huizhou.gd.cn' email,c.chinese_name ";
        sql +=
          " ,case when b.flow_code like '%_sign_%' then 'Y' else 'N' end as sign_flag";
        sql +=
          " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c";
        sql += ` where a.docid = b.docid and a.current_flow = b.flow  and b.klm = c.account and a.docid = '${paramP.docid}'`;
        const resMail = await oracle.connHrm({ sql });
        await SendMailLoop(resMail);
        res.send({ code: 200, message: "success", data: [] });
      }
    } catch {
      res.send({ code: 0, message: "系統後台異常" });
    }
  });

  router.post("/getimprovedata", (req, res) => {
    const { docid, search, type, seq } = req.body;
    let sql = "";
    if (search === "header") {
      sql = ` select improvement_theme ${
        type === "check" ? ",improvement_result" : ""
      } from xxhr_improve_acheve_improve`;
      sql += ` where docid ='${docid}' and seq = ${seq}`;
    } else {
      sql =
        "select row_number() over(order by a.project) as id,a.project,a.purpose,to_char(a.limit_start,'yyyy-mm-dd')limit_start";
      sql += `  ,to_char(a.limit_end,'yyyy-mm-dd')limit_end ${
        type === "check" ? ",a.result,a.is_adopt" : ""
      } from xxhr_improve_acheve_improve_l a`;
      sql += ` where docid='${docid}' and seq=${seq}`;
    }
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/updateaccflag", async (req, res) => {
    const { docid, seq, flow_code } = req.body;
    let sql = "";
    if (flow_code === "interview_sign_B" || flow_code === "improve_check_sign_B") {
      sql = ` update xxhr_improve_achieve_interview set user_accept_flag = 'Y' where docid = '${docid}'`;
      sql += ` and not exists(select 1 from xxhr_improve_acheve_improve where docid = '${docid}' and user_accept_flag = 'Y')`;
    } else {
      sql = ` update xxhr_improve_acheve_improve set user_accept_flag = 'Y' where docid = '${docid}' and seq=${seq}`;
      sql += ` and not exists(select 1 from xxhr_improve_acheve_improve where docid = '${docid}' and seq = ${seq} and user_accept_flag = 'Y')`;
    }
    try {
      await oracle.connHrm({ sql });
      const content =
        flow_code === "interview_sign_B" ? "績效面談表" : "改善計劃書";
      await sendMail({
        mailTo: "yufangwei@compeq.huizhou.gd.cn",
        mailSubject: "績效輔導改善系統--通知郵件",
        mailHtml: "./mail/improve_achieve/announce.ejs",
        mailData: {
          docid,
          content,
        },
      });
      res.send({ code: 200, message: "success", data: [] });
    } catch {
      res.send({ code: 0, message: "系統後台異常" });
    }
  });

  return router;
};
